Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# GROUPING

Syntax

Description of the illustration grouping.gif

Purpose

`GROUPING` distinguishes superaggregate rows from regular grouped rows. `GROUP` `BY` extensions such as `ROLLUP` and `CUBE` produce superaggregate rows where the set of all values is represented by null. Using the `GROUPING` function, you can distinguish a null representing the set of all values in a superaggregate row from a null in a regular row.

The `expr` in the `GROUPING` function must match one of the expressions in the `GROUP` `BY` clause. The function returns a value of 1 if the value of `expr` in the row is a null representing the set of all values. Otherwise, it returns zero. The datatype of the value returned by the `GROUPING` function is Oracle `NUMBER`. Please refer to the `SELECT` group_by_clause for a discussion of these terms.

Examples

In the following example, which uses the sample tables `hr.departments` and `hr.employees`, if the `GROUPING` function returns 1 (indicating a superaggregate row rather than a regular row from the table), then the string "All Jobs" appears in the "JOB" column instead of the null that would otherwise appear:

    SELECT DECODE(GROUPING(department_name), 1, 'All Departments',
       department_name) AS department,
       DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job,
       COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
       FROM employees e, departments d
       WHERE d.department_id = e.department_id
       GROUP BY ROLLUP (department_name, job_id);

    DEPARTMENT                     JOB        Total Empl Average Sal
    ------------------------------ ---------- ---------- -----------
    Accounting                     AC_ACCOUNT          1       99600
    Accounting                     AC_MGR              1      144000
    Accounting                     All Jobs            2      121800
    Administration                 AD_ASST             1       52800
    Administration                 All Jobs            1       52800
    Executive                      AD_PRES             1      288000
    Executive                      AD_VP               2      204000
    Executive                      All Jobs            3      232000
    Finance                        FI_ACCOUNT          5       95040
    Finance                        FI_MGR              1      144000
    Finance                        All Jobs            6      103200
    . . .